IF OBJECT_ID('dbo.RptCSCRActivityReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRActivityReport
    IF OBJECT_ID('dbo.RptCSCRActivityReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRActivityReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRActivityReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptCSCRActivityReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 9/17/2007
--
-- Description: Source of Report, "CSCRActivityReport"
--                  In Insight DB Reporting Services
--
-- Provides break-outs of CSCR work by agreement associate and details time 
-- required for steps in agreement execution process. The data provided by 
-- this report is needed for CSCR's reporting of activity metrics for the 
-- RV&L monthly dashboard as well as internal quality assurance/quality 
-- improvement.
--
-- Parameters:  
--     @StartDate                           
--     @EndDate                           
--     @Institution                       
--     @Department                        
--     @Unit                              
--     @CSCRCaseManager                   
--     --@ClinicalResearchOnlyYN          
--     @DomainUserId                      
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptCSCRActivityReport()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 9/17/07     KC        Created
-- 10/9/07     KC        Finished
-- 10/18/07    KC        Fixed Searching on Dates with TimeComponent
-- 01/21/08    KC        Budget with AgrCount, was counting 18 instead of 19
-- 02/27/08    KC        Removed Security per Partners Request
-- 03/25/08    KC        Added @IncludeDanaFarber
-- 04/09/08    KC        @IncludeDanaFarber now refers to Institution, not Sponsor
--
-- =======================================================================
CREATE PROCEDURE dbo.RptCSCRActivityReport
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @CSCRCaseManager                   VARCHAR(100) = NULL,
     @IncludeDanaFarber                 VARCHAR(3) = NULL,             
     @DomainUserId                      INT,
     @ShowRaw                           CHAR(1)='N'
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT,
               @InstName VARCHAR(50),
               @DeptName VARCHAR(50),
               @UnitName VARCHAR(50)

     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @CSCRCaseManager IN ('ALL','','0') SET @CSCRCaseManager = NULL
     IF @IncludeDanaFarber IN ('Yes','1','Y') SET @IncludeDanaFarber = NULL ELSE SET @IncludeDanaFarber = '2'

     -- Create table for Agreements
     CREATE TABLE #tt_Agreements
               (AgreementId             INT            NOT NULL,
               InfoEdPropNumber         VARCHAR(50)    NULL,
               CSCRCaseManager          VARCHAR(50)    NOT NULL,
               Institution              VARCHAR(50)    NULL,
               Department               VARCHAR(50)    NULL,
               Unit                     VARCHAR(50)    NULL,
               CreatedDate              DATETIME       NOT NULL,
               CSCRExecutedDate         DATETIME       NULL,
               DaysDiff                 INT            NULL)

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Create table for Deliverables
     CREATE TABLE #tt_Deliverables
               (CSCRCaseManager         VARCHAR(50)    NOT NULL,
               PIQCount                 INT            NULL,
               PIQMinDays               INT            NULL,
               PIQAvgDays               INT            NULL,
               PIQMaxDays               INT            NULL,
               AgrCount                 INT            NULL,
               AgrMinDays               INT            NULL,
               AgrAvgDays               INT            NULL,
               AgrMaxDays               INT            NULL,
               AmdCount                 INT            NULL,
               AmdMinDays               INT            NULL,
               AmdAvgDays               INT            NULL,
               AmdMaxDays               INT            NULL,
               SubConCount              INT            NULL,
               SubConMinDays            INT            NULL,
               SubConAvgDays            INT            NULL,
               SubConMaxDays            INT            NULL,
               SubAmdCount              INT            NULL,
               SubAmdMinDays            INT            NULL,
               SubAmdAvgDays            INT            NULL,
               SubAmdMaxDays            INT            NULL,
               WBudCount                INT            NULL,
               WBudMinDays              INT            NULL,
               WBudAvgDays              INT            NULL,
               WBudMaxDays              INT            NULL)

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1


     -- Populate Agreements Table
     INSERT    #tt_Agreements
               (AgreementId,
               InfoEdPropNumber,
               CSCRCaseManager,
               Institution,
               Department,
               Unit,
               CreatedDate,
               CSCRExecutedDate,
               DaysDiff)
     SELECT    AI.AgreementId AS AgreementId,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               AI.CSCRCaseManagerName AS CSCRCaseManager,
               AI.Institution AS Institution,
               AI.Department AS Department,
               AI.Unit AS Unit,
               AI.ReceivedDate AS CreatedDate,
               ISNULL(RH.FEAPDate,IEP.PropStatDate) AS CSCRExecutedDate,
               DATEDIFF(dd,AI.ReceivedDate,ISNULL(RH.FEAPDate,IEP.PropStatDate)) AS DaysDiff
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN
                         
               -- Searching on FIRST Status Change Date!
               (SELECT    VW.*
               FROM      dbo.vwInfoEdProposals VW 
                         INNER JOIN 
                         -- First time going to "CSCR - Executed"
                         (SELECT        prop_no, 
                                        MIN(PropStatDate) as FirstPropStatDate
                         FROM           dbo.vwInfoEdProposals
                         WHERE          stat_type = 0 and prop_stat = 'PARTNERS_P_PROP_STAT_66'
                         GROUP BY prop_no) FIRST ON VW.prop_no = FIRST.prop_no AND VW.PropStatDate = FIRST.FirstPropStatDate AND
                                          VW.stat_type = 0 AND VW.prop_stat = 'PARTNERS_P_PROP_STAT_66') IEP 
                                          ON AI.InfoEdPropNumber = IEP.prop_no LEFT OUTER JOIN
               -- Used for Pre-Live Fully Executed Awarded Date                                          
               dbo.RexStatusHistory RH ON AI.FolderNumber = RH.FolderNumber
                         
     WHERE     (dbo.fnRemoveTime(AI.ReceivedDate) BETWEEN  @StartDate AND @EndDate) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (AI.CSCRManagerId IS NOT NULL AND RTRIM(AI.CSCRManagerId) != '') AND
               (@CSCRCaseManager IS NULL OR AI.CSCRManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @CSCRCaseManager ))) AND

               -- PCROGroup Requests Option to IGNORE 4 DanaFarber Cancer Institute Sponsors
               --(@IncludeDanaFarber IS NULL OR AI.SponsorID NOT IN (9268,1026,1546,4715))                

               -- Change to Institution
               (@IncludeDanaFarber IS NULL OR AI.InstitutionId NOT IN (350,351))                

     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Populate Deliverables Table
     INSERT    #tt_Deliverables
               (CSCRCaseManager,
               PIQCount,
               PIQMinDays,
               PIQAvgDays,
               PIQMaxDays,
               AgrCount,
               AgrMinDays,
               AgrAvgDays,
               AgrMaxDays,
               AmdCount,
               AmdMinDays,
               AmdAvgDays,
               AmdMaxDays,
               SubConCount,
               SubConMinDays,
               SubConAvgDays,
               SubConMaxDays,
               SubAmdCount,
               SubAmdMinDays,
               SubAmdAvgDays,
               SubAmdMaxDays,
               WBudCount,
               WBudMinDays,
               WBudAvgDays,
               WBudMaxDays)
     SELECT    CSCRCaseManager,
               MAX(PIQCount),
               MAX(PIQMinDays),
               MAX(PIQAvgDays),
               MAX(PIQMaxDays),
               MAX(AgrCount),
               MAX(AgrMinDays),
               MAX(AgrAvgDays),
               MAX(AgrMaxDays),
               MAX(AmdCount),
               MAX(AmdMinDays),
               MAX(AmdAvgDays),
               MAX(AmdMaxDays),
               MAX(SubConCount),
               MAX(SubConMinDays),
               MAX(SubConAvgDays),
               MAX(SubConMaxDays),
               MAX(SubAmdCount),
               MAX(SubAmdMinDays),
               MAX(SubAmdAvgDays),
               MAX(SubAmdMaxDays),
               MAX(WBudCount),
               MAX(WBudMinDays),
               MAX(WBudAvgDays),
               MAX(WBudMaxDays)
     FROM      
     (SELECT    A.CSCRCaseManager,
               -- PIQ Stats
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_18' THEN COUNT(DEL.ReportId) 
                    ELSE 0 
               END AS PIQCount,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_18' THEN MIN(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MIN(DEL.DaysDiff) 
                    ELSE 0 
               END AS PIQMinDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_18' THEN AVG(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --AVG(DEL.DaysDiff) 
                    ELSE 0 
               END AS PIQAvgDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_18' THEN MAX(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MAX(DEL.DaysDiff) 
                    ELSE 0 
               END AS PIQMaxDays,
               -- Agr Stats
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_19' THEN COUNT(DEL.ReportId) 
                    ELSE 0 
               END AS AgrCount,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_19' THEN MIN(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MIN(DEL.DaysDiff) 
                    ELSE 0 
               END AS AgrMinDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_19' THEN AVG(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --AVG(DEL.DaysDiff) 
                    ELSE 0 
               END AS AgrAvgDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_19' THEN MAX(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MAX(DEL.DaysDiff) 
                    ELSE 0 
               END AS AgrMaxDays,
               -- Amd Stats
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_33' THEN COUNT(DEL.ReportId) 
                    ELSE 0 
               END AS AmdCount,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_33' THEN MIN(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MIN(DEL.DaysDiff) 
                    ELSE 0 
               END AS AmdMinDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_33' THEN AVG(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --AVG(DEL.DaysDiff) 
                    ELSE 0 
               END AS AmdAvgDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_33' THEN MAX(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MAX(DEL.DaysDiff) 
                    ELSE 0 
               END AS AmdMaxDays,
               -- SubCon Stats
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_34' THEN COUNT(DEL.ReportId) 
                    ELSE 0 
               END AS SubConCount,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_34' THEN MIN(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MIN(DEL.DaysDiff) 
                    ELSE 0 
               END AS SubConMinDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_34' THEN AVG(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --AVG(DEL.DaysDiff) 
                    ELSE 0 
               END AS SubConAvgDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_34' THEN MAX(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MAX(DEL.DaysDiff) 
                    ELSE 0 
               END AS SubConMaxDays,
               -- SubAmd Stats
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_36' THEN COUNT(DEL.ReportId) 
                    ELSE 0 
               END AS SubAmdCount,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_36' THEN MIN(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MIN(DEL.DaysDiff) 
                    ELSE 0 
               END AS SubAmdMinDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_36' THEN AVG(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --AVG(DEL.DaysDiff) 
                    ELSE 0 
               END AS SubAmdAvgDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_36' THEN MAX(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MAX(DEL.DaysDiff) 
                    ELSE 0 
               END AS SubAmdMaxDays,
               -- WBud Stats
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_30' THEN COUNT(DEL.ReportId) 
                    ELSE 0 
               END AS WBudCount,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_30' THEN MIN(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MIN(DEL.DaysDiff) 
                    ELSE 0 
               END AS WBudMinDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_30' THEN AVG(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --AVG(DEL.DaysDiff) 
                    ELSE 0 
               END AS WBudAvgDays,
               CASE DEL.REPORTCAT 
                    WHEN 'PARTNERS_P_REPORTCAT_30' THEN MAX(DATEDIFF(dd,A.CreatedDate,DEL.COMPLETEDDATE)) --MAX(DEL.DaysDiff) 
                    ELSE 0 
               END AS WBudMaxDays               
     FROM      #tt_Agreements A INNER JOIN dbo.vwInfoEdCSCRDeliverablesCompleted DEL ON A.InfoEdPropNumber = DEL.prop_no
     GROUP BY  A.CSCRCaseManager,
               DEL.REPORTCAT
     ) MINMAXAVG
     GROUP BY CSCRCaseManager
     
     
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Get InstName, DeptName, UnitName
     IF @Institution IS NOT NULL AND IsNumeric(@Institution)=1
          SELECT TOP 1 @InstName = Institution
          FROM      #tt_Agreements

     IF @Department IS NOT NULL AND IsNumeric(@Department)=1
          SELECT TOP 1 @DeptName = Department
          FROM      #tt_Agreements

     IF @Unit IS NOT NULL AND IsNumeric(@Unit)=1
          SELECT TOP 1 @UnitName = Unit
          FROM      #tt_Agreements


     -- Return ResultSet
     SELECT    AC1.CSCRCaseManager AS CSCRCaseManager,
               AC1.InstSearchName AS InstSearchName,
               AC1.DeptSearchName AS DeptSearchName,
               AC1.UnitSearchName AS UnitSearchName,
               ISNULL(AC1.MasterCreatedCount,0) AS MasterCreatedCount,
               ISNULL(AC2.CSCRExecutedCount,0) AS CSCRExecutedCount,
               ISNULL(AC2.MinMasterDaysExecuted,0) AS MinMasterDaysExecuted,
               ISNULL(AC2.AvgMasterDaysExecuted,0) AS AvgMasterDaysExecuted,
               ISNULL(AC2.MaxMasterDaysExecuted,0) AS MaxMasterDaysExecuted,
               ISNULL(DEL.PIQCount,0) AS PIQCount,
               ISNULL(DEL.PIQMinDays,0) AS PIQMinDays,
               ISNULL(DEL.PIQAvgDays,0) AS PIQAvgDays,
               ISNULL(DEL.PIQMaxDays,0) AS PIQMaxDays,
               ISNULL(DEL.AgrCount,0) AS AgrCount,
               ISNULL(DEL.AgrMinDays,0) AS AgrMinDays,
               ISNULL(DEL.AgrAvgDays,0) AS AgrAvgDays,
               ISNULL(DEL.AgrMaxDays,0) AS AgrMaxDays,
               ISNULL(DEL.AmdCount,0) AS AmdCount,
               ISNULL(DEL.AmdMinDays,0) AS AmdMinDays,
               ISNULL(DEL.AmdAvgDays,0) AS AmdAvgDays,
               ISNULL(DEL.AmdMaxDays,0) AS AmdMaxDays,
               ISNULL(DEL.SubConCount,0) AS SubConCount,
               ISNULL(DEL.SubConMinDays,0) AS SubConMinDays,
               ISNULL(DEL.SubConAvgDays,0) AS SubConAvgDays,
               ISNULL(DEL.SubConMaxDays,0) AS SubConMaxDays,
               ISNULL(DEL.SubAmdCount,0) AS SubAmdCount,
               ISNULL(DEL.SubAmdMinDays,0) AS SubAmdMinDays,
               ISNULL(DEL.SubAmdAvgDays,0) AS SubAmdAvgDays,
               ISNULL(DEL.SubAmdMaxDays,0) AS SubAmdMaxDays,
               ISNULL(DEL.WBudCount,0) AS WBudCount,
               ISNULL(DEL.WBudMinDays,0) AS WBudMinDays,
               ISNULL(DEL.WBudAvgDays,0) AS WBudAvgDays,
               ISNULL(DEL.WBudMaxDays,0) AS WBudMaxDays
               
     FROM      (-- Overall Count
               SELECT    CSCRCaseManager,
                         @InstName AS InstSearchName,
                         @DeptName AS DeptSearchName,
                         @UnitName AS UnitSearchName,
                         COUNT(AgreementId) AS MasterCreatedCount
               FROM      #tt_Agreements 
               GROUP BY  CSCRCaseManager ) AC1 
               LEFT OUTER JOIN (-- CSCRExecutedCount
                              SELECT    CSCRCaseManager,
                                        COUNT(AgreementId) AS CSCRExecutedCount,
                                        MIN(DaysDiff) AS MinMasterDaysExecuted,
                                        AVG(DaysDiff) AS AvgMasterDaysExecuted,
                                        MAX(DaysDiff) AS MaxMasterDaysExecuted
                              FROM      #tt_Agreements
                              WHERE     CSCRExecutedDate IS NOT NULL
                              GROUP BY  CSCRCaseManager ) AC2 ON AC1.CSCRCaseManager = AC2.CSCRCaseManager

               LEFT OUTER JOIN #tt_Deliverables DEL ON AC1.CSCRCaseManager = DEL.CSCRCaseManager
    
     ORDER BY AC1.CSCRCaseManager
     
     -- Raw Data
     IF @ShowRaw = 'Y'
     BEGIN
          SELECT * from #tt_Agreements ORDER BY CSCRCaseManager
          SELECT * from #tt_Deliverables ORDER BY CSCRCaseManager
     END
     
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptCSCRActivityReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRActivityReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRActivityReport >>>'
go

GRANT EXECUTE ON dbo.RptCSCRActivityReport TO InsightReport,Insight
go
